--    Author    : ChenErHao
--    Name      : EDW.AG_DEPB_INFO.HQL
--    Functions : 
--    Purpose   : Monthly saving the variation data from ods
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-05-03  ChenErHao           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE EDW.AG_DEPB_INFO PARTITION (DATA_DATE = '#V_DATA_DATE#' ,DATA_SRC_ORG = '#V_DATA_SRC_ORG#')
SELECT 
    FIN_ORG_NO                          -- 金融机构编码
    ,CLIENT_TYPE                        -- 客户类型
    ,BASE_ACCT_NO                       -- 存款账户代码
    ,SUB_ACCT_NO                        -- 存款协议代码
    ,DEPOSIT_TYPE                       -- 产品类别
    ,VALUE_DATE                         -- 存款协议起始日
    ,MATURITY_DATE                      -- 存款协议到期日
    ,CCY                                -- 存款币种
    ,NVL(ACTUAL_BAL,0) AS ACTUAL_BAL    -- 存款余额
    ,IF_FLOAT                           -- 利率是否固定
    ,RATE                               -- 利率水平
    ,CASE
        WHEN DEP.DEPOSIT_TYPE IS NULL OR DEP.DEPOSIT_TYPE = '' OR DEP.MATURITY_DATE IS NULL OR DEP.MATURITY_DATE = '' THEN '10'
        WHEN DEP.DEPOSIT_TYPE NOT IN ('D012','D014','D04','D052') THEN '10'
        WHEN F_MONTHDIFF(DEP.VALUE_DATE,DEP.MATURITY_DATE) <= 1   THEN '1'
        WHEN F_MONTHDIFF(DEP.VALUE_DATE,DEP.MATURITY_DATE) <= 3   THEN '2'
        WHEN F_MONTHDIFF(DEP.VALUE_DATE,DEP.MATURITY_DATE) <= 6   THEN '3'
        WHEN F_MONTHDIFF(DEP.VALUE_DATE,DEP.MATURITY_DATE) <= 12  THEN '4'
        WHEN F_MONTHDIFF(DEP.VALUE_DATE,DEP.MATURITY_DATE) <= 24  THEN '5'
        WHEN F_MONTHDIFF(DEP.VALUE_DATE,DEP.MATURITY_DATE) <= 36  THEN '6'
        WHEN F_MONTHDIFF(DEP.VALUE_DATE,DEP.MATURITY_DATE) <= 60  THEN '7'
        WHEN F_MONTHDIFF(DEP.VALUE_DATE,DEP.MATURITY_DATE) <= 120 THEN '8'
        WHEN F_MONTHDIFF(DEP.VALUE_DATE,DEP.MATURITY_DATE) > 120  THEN '9'
        ELSE '10'
     END AS TERM_CODE                   -- 存款期限
    ,CASE 
        WHEN DEP.ACTUAL_BAL IS NULL OR DEP.ACTUAL_BAL = '' THEN 'A000'
        WHEN DEP.ACTUAL_BAL <= 10000   THEN 'A000'
        WHEN DEP.ACTUAL_BAL <= 50000   THEN 'A001'
        WHEN DEP.ACTUAL_BAL <= 100000  THEN 'A005'
        WHEN DEP.ACTUAL_BAL <= 200000  THEN 'A010'
        WHEN DEP.ACTUAL_BAL <= 50000   THEN 'A020'
        WHEN DEP.ACTUAL_BAL <= 1000000 THEN 'A050'
        WHEN DEP.ACTUAL_BAL > 1000000  THEN 'A100'
     END AS INTERVAL_CODE1              -- 余额区间
    ,CASE WHEN DEP.VALUE_DATE LIKE CONCAT(SUBSTRING('#V_DATA_DATE#',1,6),'%') THEN NVL(DEP.ACTUAL_BAL,0) ELSE 0 END AS ACTUAL_AMOUNT  -- 当月发生额
    ,CASE
        WHEN SUBSTRING(DEP.VALUE_DATE,1,6) <> SUBSTRING('#V_DATA_DATE#',1,6) THEN 'A000'
        WHEN DEP.ACTUAL_BAL IS NULL OR DEP.ACTUAL_BAL = '' THEN 'A000'
        WHEN DEP.ACTUAL_BAL <= 10000   THEN 'A000'
        WHEN DEP.ACTUAL_BAL <= 50000   THEN 'A001'
        WHEN DEP.ACTUAL_BAL <= 100000  THEN 'A005'
        WHEN DEP.ACTUAL_BAL <= 200000  THEN 'A010'
        WHEN DEP.ACTUAL_BAL <= 50000   THEN 'A020'
        WHEN DEP.ACTUAL_BAL <= 1000000 THEN 'A050'
        WHEN DEP.ACTUAL_BAL > 1000000  THEN 'A100'
     END AS INTERVAL_CODE2             -- 发生额区间
    ,NVL(DEP.ACTUAL_BAL,0)*NVL(DEP.RATE,0) AS WSUM_BAL  -- 余额加权值
    ,CASE WHEN DEP.VALUE_DATE LIKE CONCAT(SUBSTRING('#V_DATA_DATE#',1,6),'%') THEN NVL(DEP.ACTUAL_BAL,0)*NVL(DEP.RATE,0) ELSE 0 END AS WSUM_AMOUNT   -- 当月发生额加权值
    ,CASE  
        WHEN DEPOSIT_TYPE IN('D012','D014') THEN 'A'
        WHEN DEPOSIT_TYPE IN('D011','D013') THEN 'B'
        ELSE 'Z'
     END AS CF_FLAG                  -- 定活标识 
    ,ORG.ORG_CODE_2 AS ORG_TYPE      -- 机构类型
FROM ODS.SUST_DLMG_DEPB DEP
LEFT JOIN DIMENSION.T_ORG_BIZ_LVL_BAK ORG ON(DEP.FIN_ORG_NO=ORG.ORG_CODE_4)
WHERE DEP.DATA_DATE = '#V_DATA_DATE#' AND DEP.DATA_SRC_ORG = '#V_DATA_SRC_ORG#'
;